package com.x.erp.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.x.erp.model.SaleBill;
import com.x.framework.dao.BaseDao;

@Repository
public class SaleBillDao extends BaseDao {

	//查询列表sql
	private final static String selectSaleBillListSql = "SELECT " + 
			"esb.*, " + 
			"bct.CAR_TYPE_NAME, " + 
			"bp.PROVINCE_NAME, " + 
			"bc.CITY_NAME, " + 
			"em.MARKET_NAME, " + 
			"bb.BRAND_NAME, " + 
			"bs.SERIES_NAME, " + 
			"bm.MODEL_NAME, " + 
			"es.SHOP_NAME "+ 
			"FROM " + 
			"ERP_sale_bill esb " + 
			"JOIN ERP_MARKET em ON esb.MARKET_ID = em.MARKET_ID " + 
			"JOIN ERP_MARKET emp ON emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " + 
			"LEFT JOIN base_province bp ON esb.PROVINCE_ID = bp.PROVINCE_ID " + 
			"LEFT JOIN base_city bc ON esb.CITY_ID = bc.CITY_ID " + 
			"LEFT JOIN base_car_type bct ON esb.CAR_TYPE_ID = bct.CAR_TYPE_ID " + 
			"LEFT JOIN x_base.base_brand bb ON esb.BRAND_ID = bb.BRAND_ID " +
			"LEFT JOIN x_base.base_series bs ON esb.SERIES_ID = bs.SERIES_ID " +
			"LEFT JOIN x_base.base_model bm ON esb.MODEL_ID = bm.MODEL_ID " +
			"LEFT JOIN ERP_shop es ON esb.SHOP_ID = es.SHOP_ID " + 
			"WHERE 1=1 ";
	//查询数量sql
	private String selectSaleBillNumSql = "SELECT " + 
			"COUNT(esb.BILL_ID) "+ 
			"FROM " + 
			"ERP_sale_bill esb " + 
			"JOIN ERP_market em ON esb.MARKET_ID = em.MARKET_ID " + 
			"JOIN ERP_MARKET emp ON emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " + 
			"LEFT JOIN base_province bp ON esb.PROVINCE_ID = bp.PROVINCE_ID " + 
			"LEFT JOIN base_city bc ON esb.CITY_ID = bc.CITY_ID " + 
			"LEFT JOIN base_car_type bct ON esb.CAR_TYPE_ID = bct.CAR_TYPE_ID " + 
			"LEFT JOIN x_base.base_brand bb ON esb.BRAND_ID = bb.BRAND_ID " +
			"LEFT JOIN x_base.base_series bs ON esb.SERIES_ID = bs.SERIES_ID " +
			"LEFT JOIN x_base.base_model bm ON esb.MODEL_ID = bm.MODEL_ID " +
			"LEFT JOIN ERP_shop es ON esb.SHOP_ID = es.SHOP_ID " + 
			"WHERE 1=1 ";

	/**
	 * @param saleBill 封装了saleBill的检索条件
	 * @return 
	 * @throws Exception
	 */
	public List<SaleBill> selectSaleBillList(SaleBill saleBill) throws Exception{
		StringBuilder sb = new StringBuilder(selectSaleBillListSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		if(saleBill != null){
			searchCondition(sb, params, saleBill);
		}
		sb.append("ORDER BY esb.BILL_CREATE_TIME DESC ");
		return this.queryForList(sb.toString(), SaleBill.class, params, saleBill);
	}

	/**
	 * 查询数量
	 * @param saleBill
	 * @return
	 */
	public int selectSaleBillNum(SaleBill saleBill) {
		StringBuilder sb = new StringBuilder(selectSaleBillNumSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		if(saleBill != null){
			searchCondition(sb, params, saleBill);
		}
		return this.queryForInt(sb.toString(), params);
	}

	/**
	 * 查询条件
	 * @param sql Sql
	 * @param saleBill 查询对象
	 * @param params 参数
	 */
	private void searchCondition(StringBuilder sql, List<Object> params, SaleBill saleBill) {
		if(this.isNotNull(saleBill.getBillId())){
			sql.append("AND esb.BILL_ID = ? ");
			params.add(saleBill.getBillId());
		}
		//车型
		if(this.isNotNull(saleBill.getCarTypeId())){
			sql.append("AND esb.CAR_TYPE_ID = ? ");
			params.add(saleBill.getCarTypeId());
		}
		//开票日期
		if(saleBill.getBillDate() != null){
			sql.append("AND esb.BILL_DATE = ? ");
			params.add(saleBill.getBillDate());
		}
		if(this.isNotNull(saleBill.getBillCarVin())){
			sql.append("AND esb.BILL_CAR_VIN = ? ");
			params.add(saleBill.getBillCarVin());
		}
		if(this.isNotNull(saleBill.getBillCarColor())){
			sql.append("AND esb.BILL_CAR_COLOR = ? ");
			params.add(saleBill.getBillCarColor());
		}
		if(saleBill.getBrandId() != null){
			sql.append("AND esb.BRAND_ID = ? ");
			params.add(saleBill.getBrandId());
		}
		if(saleBill.getSeriesId() != null){
			sql.append("AND esb.SERIES_ID = ? ");
			params.add(saleBill.getSeriesId());
		}
		if(saleBill.getModelId() != null){
			sql.append("AND esb.MODEL_ID = ? ");
			params.add(saleBill.getModelId());
		}
		if(this.isNotNull(saleBill.getProvinceId())){
			sql.append("AND esb.PROVINCE_ID = ? ");
			params.add(saleBill.getProvinceId());
		}
		if(this.isNotNull(saleBill.getCityId())){
			sql.append("AND esb.CITY_ID = ? ");
			params.add(saleBill.getCityId());
		}
		if(this.isNotNull(saleBill.getShopId())){
			sql.append("AND esb.SHOP_ID = ? ");
			params.add(saleBill.getShopId());
		}
		if(this.isNotNull(saleBill.getUserId())){
			sql.append("AND esb.USER_ID = ? ");
			params.add(saleBill.getUserId());
		}
	}

	public final static String selectTodaySaleInfoSql = "SELECT " +
			"COUNT(BILL_ID) AS saleNum,IFNULL(SUM(BILL_PRICE),0) AS saleVolume " +
			"FROM " +
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp " +
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND esb.BILL_DATE = ? ";
	/**
	 * 查询今日成交量
	 * @param saleBill
	 * @return
	 */
	public Map<String, Object> selectTodaySaleInfo(SaleBill saleBill) throws Exception{
		StringBuilder sql = new StringBuilder(selectTodaySaleInfoSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//获取今天的日期
		params.add(getCurrentDate(0, "yyyy-MM-dd"));
		if(saleBill != null) {
			if(this.isNotNull(saleBill.getShopId())) {
				sql.append("AND esb.SHOP_ID = ? ");
				params.add(saleBill.getShopId());
			}
		}
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("saleVolume", rs.getString("saleVolume"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	/**
	 * 查询本月成交量
	 * @param saleBill
	 * @return
	 */
	public final static String selectThisMonthSaleInfoSql = "SELECT " +
			"COUNT(BILL_ID) AS saleNum,IFNULL(SUM(BILL_PRICE),0) AS saleVolume " +
			"FROM " +
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp " +
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND esb.BILL_DATE >= ? AND esb.BILL_DATE < ? " ;
	public Map<String, Object> selectThisMonthSaleInfo(SaleBill saleBill) throws Exception{
		StringBuilder sql = new StringBuilder(selectThisMonthSaleInfoSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//获取本月第一天的时间
		params.add(getCurrentDate(0, "yyyy-MM") + "-01");
		//获取当前时间
		params.add(getDate(0));
		if(saleBill != null) {
			if(this.isNotNull(saleBill.getShopId())) {
				sql.append("AND esb.SHOP_ID = ? ");
				params.add(saleBill.getShopId());
			}
		}
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("saleVolume", rs.getString("saleVolume"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	public final static String selectTotalSaleInfoSql = "SELECT " +
			"COUNT(esb.BILL_ID) AS saleNum,IFNULL(SUM(BILL_PRICE),0) AS saleVolume " +
			"FROM " +
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp " +
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询累计成交量
	 * @param saleBill
	 * @return
	 */
	public Map<String, Object> selectTotalSaleInfo(SaleBill saleBill) throws Exception{
		StringBuilder sql = new StringBuilder(selectTotalSaleInfoSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		if(saleBill != null) {
			if(this.isNotNull(saleBill.getShopId())) {
				sql.append("AND esb.SHOP_ID = ? ");
				params.add(saleBill.getShopId());
			}
		}
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("saleVolume", rs.getString("saleVolume"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	public final static String selectSaleInfoByConditionSql = "SELECT " +
			"COUNT(esb.BILL_ID) AS saleNum,IFNULL(SUM(BILL_PRICE),0) AS saleVolume " +
			"FROM " +
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp " +
			"WHERE "+
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 条件查询成交信息
	 * @param saleBill
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Map<String, Object> selectSaleInfoByCondition(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectSaleInfoByConditionSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("saleVolume", rs.getString("saleVolume"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	public final static String selectMarketSaleInfoSql = "SELECT " + 
			"esb.MARKET_ID AS marketId, " + 
			"em.MARKET_NAME AS marketName, " + 
			"em.MARKET_SHORT_NAME AS marketShortName, " +
			"IFNULL(SUM(esb.BILL_PRICE),0) AS saleVolume, " +
			"COUNT(esb.MARKET_ID) AS saleNum " + 
			"FROM " + 
			"ERP_sale_bill esb, " + 
			"ERP_market em " +
			"WHERE " + 
			"esb.MARKET_ID = em.MARKET_ID ";
	/**
	 * 查询市场销售情况
	 * @param saleBill
	 * @param startDate 开始时间
	 * @param endDate 结束时间
	 * @return
	 */
	public List<Map<String, Object>> selectMarketSaleInfo(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectMarketSaleInfoSql);
		List<Object> params = new ArrayList<Object>();
		if(startDate != null) {
			sql.append("AND esb.BILL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND esb.BILL_DATE <= ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY esb.MARKET_ID ");
		sql.append("ORDER BY saleNum ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("marketId", rs.getString("marketId"));
				map.put("marketName", rs.getString("marketName"));
				map.put("marketShortName", rs.getString("marketShortName"));
				map.put("saleNum", rs.getInt("saleNum"));
				map.put("saleVolume", rs.getInt("saleVolume"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	public final static String selectShopSaleRankSql = "SELECT " + 
			"esb.SHOP_ID AS shopId, " + 
			"es.SHOP_NAME AS shopName, " + 
			"COUNT(esb.SHOP_ID) AS saleNum " + 
			"FROM " + 
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp, " +
			"ERP_SHOP es " + 
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND esb.SHOP_ID = es.SHOP_ID ";
	/**
	 * 查询商铺销售排行
	 * @param saleBill
	 * @param startDate 开始时间
	 * @param endDate 结束时间
	 * @return
	 */
	public List<Map<String, Object>> selectShopSaleRank(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectShopSaleRankSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		sql.append("GROUP BY esb.SHOP_ID ");
		sql.append("ORDER BY saleNum DESC ");
		//分页Sql
		getLimitSql(sql, saleBill.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("shopId", rs.getString("shopId"));
				map.put("shopName", rs.getString("shopName"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	public final static String selectSalesVolumeRankSql = "SELECT " + 
			"esb.SHOP_ID AS shopId, " + 
			"es.SHOP_NAME AS shopName, " + 
			"SUM(esb.BILL_PRICE) AS salesVolume " + 
			"FROM " + 
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp, " +
			"ERP_SHOP es " + 
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND esb.SHOP_ID = es.SHOP_ID ";
	/**
	 * 查询销售额排行
	 * @param saleBill
	 * @param startDate 开始时间
	 * @param endDate 结束时间
	 * @return
	 */
	public List<Map<String, Object>> selectSalesVolumeRank(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectSalesVolumeRankSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		sql.append("GROUP BY esb.SHOP_ID ");
		sql.append("ORDER BY salesVolume DESC ");
		//分页Sql
		getLimitSql(sql, saleBill.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("shopId", rs.getString("shopId"));
				map.put("shopName", rs.getString("shopName"));
				map.put("salesVolume", rs.getInt("salesVolume"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	public final static String selectBrandRankSql = "SELECT " + 
			"esb.BRAND_ID AS brandId, " + 
			"bb.BRAND_NAME AS brandName, " + 
			"COUNT(esb.BRAND_ID) AS saleNum " + 
			"FROM " + 
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp, " +
			"x_base.BASE_BRAND bb " +
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND esb.BRAND_ID = bb.BRAND_ID ";
	/**
	 * 查询畅销品牌排行
	 * @param saleBill
	 * @param startDate 开始时间
	 * @param endDate 结束时间
	 * @return
	 */
	public List<Map<String, Object>> selectBrandRank(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectBrandRankSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		sql.append("GROUP BY esb.BRAND_ID ");
		sql.append("ORDER BY saleNum DESC ");
		//分页Sql
		getLimitSql(sql, saleBill.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("brandId", rs.getString("brandId"));
				map.put("brandName", rs.getString("brandName"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	public final static String selectModelRankSql = "SELECT " + 
			"esb.MODEL_ID AS modelId, " + 
			"bb.BRAND_NAME AS brandName, " + 
			"bs.SERIES_NAME AS seriesName, " + 
			"bm.MODEL_NAME AS modelName, " + 
			"COUNT(esb.MODEL_ID) AS saleNum " + 
			"FROM " + 
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp, " +
			"x_base.base_brand bb, " +
			"x_base.base_series bs, " +
			"x_base.base_model bm " +
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND esb.BRAND_ID = bb.BRAND_ID " +
			"AND esb.SERIES_ID = bs.SERIES_ID " + 
			"AND esb.MODEL_ID = bm.MODEL_ID ";
	/**
	 * 查询车型排行
	 * @param saleBill
	 * @param startDate 开始时间
	 * @param endDate 结束时间
	 */
	public List<Map<String, Object>> selectModelRank(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectModelRankSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		sql.append("GROUP BY esb.MODEL_ID ");
		sql.append("ORDER BY saleNum DESC ");
		//分页Sql
		getLimitSql(sql, saleBill.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("modelId", rs.getString("modelId"));
				map.put("brandName", rs.getString("brandName"));
				map.put("seriesName", rs.getString("seriesName"));
				map.put("modelName", rs.getString("modelName"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	public final static String selectColorRankSql = "SELECT " + 
			"esb.BILL_CAR_COLOR AS carColor, " + 
			"COUNT(esb.BILL_CAR_COLOR) AS saleNum " + 
			"FROM " + 
			"ERP_SALE_BILL esb, "  +
			"ERP_MARKET em, " +
			"ERP_MARKET emp " +
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询颜色排行
	 * @param saleBill
	 * @param startDate 开始时间
	 * @param endDate 结束时间
	 * @return
	 */
	public List<Map<String, Object>> selectColorRank(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectColorRankSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		sql.append("GROUP BY esb.BILL_CAR_COLOR ");
		sql.append("ORDER BY saleNum DESC ");
		//分页Sql
		getLimitSql(sql, saleBill.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("carColor", rs.getString("carColor"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	public final static String selectPriceRankSql = "SELECT " + 
			"bp.PRICE_REMARK AS priceRemark, " + 
			"COUNT(esb.BILL_ID) AS saleNum " + 
			"FROM " + 
			"ERP_SALE_BILL esb, " +
			"ERP_MARKET em, " +
			"ERP_MARKET emp, " +
			"BASE_PRICE bp " + 
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND esb.PRICE_ID = bp.PRICE_ID ";
	/**
	 * 查询价格排行
	 * @param saleBill
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectPriceRank(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectPriceRankSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		sql.append("GROUP BY esb.PRICE_ID ");
		sql.append("ORDER BY saleNum DESC ");
		//分页Sql
		getLimitSql(sql, saleBill.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("priceRemark", rs.getString("priceRemark"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	public final static String selectAreaSaleInfoSql = "SELECT " + 
			"em.MARKET_NAME AS marketName, " + 
			"bc.CITY_NAME AS cityName, " + 
			"COUNT(esb.BILL_ID) AS saleNum " + 
			"FROM " + 
			"ERP_SALE_BILL esb, " + 
			"ERP_MARKET em, " +
			"ERP_MARKET emp, " +
			"BASE_CITY bc " + 
			"WHERE " + 
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND em.CITY_ID = bc.CITY_ID ";
	/**
	 * 查询区域销售信息
	 * @param saleBill
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectAreaSaleInfo(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectAreaSaleInfoSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		sql.append("GROUP BY esb.MARKET_ID ");
		sql.append("ORDER BY saleNum DESC ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("marketName", rs.getString("marketName"));
				map.put("cityName", rs.getString("cityName"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}
	
	public final static String selectMonthSaleInfoSql = "SELECT " +
			"DATE_FORMAT(BILL_DATE,'%Y-%m') AS monthStr," +
			"COUNT(esb.BILL_ID) AS saleNum," +
			"IFNULL(SUM(BILL_PRICE),0) AS saleVolume " +
			"FROM " +
			"ERP_SALE_BILL esb, "  +
			"ERP_MARKET em, " +
			"ERP_MARKET emp " +
			"WHERE " +
			"esb.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询月销售信息
	 * @param saleBill
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectMonthSaleInfo(SaleBill saleBill, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectMonthSaleInfoSql);
		List<Object> params = new ArrayList<Object>();
		params.add(saleBill.getMarketId());
		//组装查询条件
		statisSearchCondition(sql, params, saleBill, startDate, endDate);
		sql.append("GROUP BY monthStr ");
		sql.append("ORDER BY monthStr ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("monthStr", rs.getString("monthStr"));
				map.put("saleVolume", rs.getString("saleVolume"));
				map.put("saleNum", rs.getInt("saleNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	/**
	 * 统计查询条件
	 * @param sql
	 * @param params
	 * @param saleBill
	 * @param startDate
	 * @param endDate
	 */
	private void statisSearchCondition(StringBuilder sql, List<Object> params, SaleBill saleBill, Date startDate, Date endDate) {
		if(saleBill != null) {
			if(this.isNotNull(saleBill.getShopId())) {
				sql.append("AND esb.SHOP_ID = ? ");
				params.add(saleBill.getShopId());
			}
		}
		if(startDate != null) {
			sql.append("AND esb.BILL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND esb.BILL_DATE <= ? ");
			params.add(endDate);
		}
	}

	/**
	 * 分页Sql
	 * @param sql
	 * @param pageSize
	 */
	private void getLimitSql(StringBuilder sql, Integer pageSize) {
		if(pageSize != null && pageSize > 0) {
			sql.append("LIMIT ").append(pageSize);
		} else {
			sql.append("LIMIT 5");
		}
	}
}
